package cn.zxd.util;



import cn.zxd.pub.PubFun;

import java.util.ArrayList;
import java.util.List;

/**
 * 解析传过来的参数（sql）
 * 将参数赋值到带参数的sql中
 * 返回参数
 * @author hyy
 *
 */
public class EasyQuerySql {
    private String JspName;
    private String SqlId;
    private List ParaName=new ArrayList();
    private List ParaValue=new ArrayList();
    private int ParaCount=0;

    public EasyQuerySql() {
    }

    //解析传入的串，格式jspname;sqlid;paraname1=paravalue1;paraname2=paravalue2;....
    public boolean parsePara(String strPara)
    {
        //解析jspname
        String tStr = PubFun.getStr(strPara, 1, ";");
        if (tStr.equals(""))
            return false;
        this.JspName=tStr;
        strPara = strPara.substring(tStr.length()+1);
        //解析sqlid
        tStr = PubFun.getStr(strPara, 1, ";");
        if (tStr.equals(""))
            return false;
        this.SqlId=tStr;
        strPara = strPara.substring(tStr.length()+1);
        //解析参数
        String strOnePara,strName,strValue;
        try {
            while (true) {
                tStr = PubFun.getStr(strPara, 1, ";");
                if (tStr.equals(""))
                    break;
                strOnePara=tStr;
                strName = PubFun.getStr(strOnePara, 1, "=");
                if(strName.equals(""))
                    break;
                strValue=strOnePara.substring(strName.length()+1);
                if(strValue!=null&&!strValue.equals(""))
                {
                    if (!checkValidation(strName,strValue))
                    {
                        return false;
                    }
                    this.ParaName.add(strName);
                    this.ParaValue.add(strValue);
                    this.ParaCount++;
                }
                strPara = strPara.substring(tStr.length()+1);
            }
        }
        catch (Exception ex) {
            // @@错误处理
            CError tError = new CError();
            tError.moduleName = "Calculator";
            tError.functionName = "interpretFactorInSQL";
            tError.errorMessage = "@utilityEasyQuerySql010@" + strPara + "@utilityEasyQuerySql020@" + tStr + "@utilityEasyQuerySql030@";
            return false;
        }
        return true;
    }




    /**
     * 验证sql的正确性
     * @param aName
     * @param aPara
     * @return true/false
     */
    private boolean checkValidation(String aName,String aPara)
    {
        String tPara = aPara.toUpperCase();
        boolean vFlag = true;
        if (aName!=null&&aName.toUpperCase().equals("GET") )
        {
            return true;
        }
        if (tPara.indexOf("SELECT") > -1 && tPara.indexOf("FROM") > -1) {
            vFlag=false;
        }
        if (tPara.indexOf('<') != -1) {
            vFlag = false;
        } else if (tPara.indexOf('=') != -1) {
            vFlag = false;
        } else if (tPara.indexOf('>') != -1) {
            vFlag = false;
        } else if (tPara.indexOf('%') != -1) {
            vFlag = false;
        } else if (tPara.indexOf('\'') != -1) {
            vFlag = false;
        } else if (tPara.indexOf('"') != -1) {
            vFlag = false;
        } else if (tPara.indexOf(';') != -1) {
            vFlag = false;
        } else if (tPara.indexOf('(') != -1) {
            vFlag = false;
        } else if (tPara.indexOf(')') != -1) {
            vFlag = false;
        } else if (tPara.indexOf('+') != -1) {
            vFlag = false;
        }
        else if (tPara.indexOf('\'') != -1) {
            vFlag = false;
        }

//      System.out.println("aPara:"+aPara+" is "+vFlag) ;
        if (!vFlag)
        {
            CError tError = new CError();
            tError.moduleName = "Calculator";
            tError.functionName = "interpretFactorInSQL";
            tError.errorMessage = "@utilityEasyQuerySql040@" + aPara + "@utilityEasyQuerySql050@";
            return vFlag;
        }
        return vFlag;
    }

    /**
     * 注意在传入的sql中参数都是以如下方式来写的
     * 转换sql中的?paraname?为传入的值
     */
    public String convertToValue(String strSql)
    {
        String strValue="";
        String tSql, tStr = "", tStr1 = "";
        tSql = strSql;
        while (true) {
            tStr = PubFun.getStr(tSql, 2, "?");
            if (tStr.equals("")) {
                break;
            }
            tStr1 = "?" + tStr.trim() + "?";
            strValue=getValueByName(tStr);
            if(strValue.equals(""))//如果有一个参数未找到对应的值，则整个串都为空
            {
                break;
            }
            //替换变量
            tSql = StrTool.replaceEx(tSql, tStr1, strValue);
        }
        return tSql;
    }

    //根据名称检索传入的参数值
    private String getValueByName(String cVarName)
    {
        String strName="";
        String strValue="";
        for (int i = 0; i < this.ParaName.size(); i++) {
            strName=(String)ParaName.get(i);
            if(strName.toUpperCase().equals(cVarName.toUpperCase()))
            {
                strValue=(String)ParaValue.get(i);
                break;
            }
        }
        return strValue;
    }

    public String getJspName() {
        return JspName;
    }

    public String getParaName(int index) {
        return (String)this.ParaName.get(index);
    }

    public String getParaValue(int index) {
        return (String)this.ParaValue.get(index);
    }

    public String getSqlId() {
        return SqlId;
    }

    public static void main(String[] args) {
        EasyQuerySql t=new EasyQuerySql();

        t.parsePara("a.jsp;sql1;Name=hyy;sex=man;");
        String sql = t.convertToValue("select CustomerNo, Name, Sex, Birthday, IDType, IDNo from LDPerson where Sex='?sex?' and name like '?Name?%'");
        System.out.println(sql);
        //t.generateSql("selec * from dual where 1=1{and polno=?b?{and contno=?c?{and grpconno=?d?}}"
        //+ "and 2=2{and rskno=?f?}");
    }

    public int getParaCount() {
        return ParaCount;
    }
}
